package a_o2odata_deal.utils

import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}
import a_aa_amainpackage.a_o2odata_deal.config.config._
/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/1/7 18:42
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object modify_xiajia {

  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    sqlContext.udf.register("min_price",min_price _)
    //加载天猫数据
    sqlContext.read.orc(s"${source_result}").createOrReplaceTempView("source")

    val source_data = sqlContext.sql(
      s"""
         |select
         |*,
         |cast(salesAmount as double)/cast(sellCount as double) as price_avg
         |from source
       """.stripMargin)
      .withColumnRenamed("priceText","priceText_v1")
      .withColumnRenamed("price_avg","priceText")

      //.selectExpr("good_id","sellCount","priceText","salesAmount","is_newProduct")
    import org.apache.spark.sql.functions._
    source_data.agg(count("*"),sum("sellCount"),sum("salesAmount")).show(false)

    source_data.createOrReplaceTempView("source_data")

    //加载是否是新品集合
    val newProduct_data = sqlContext.
      read.json(s"${a_aa_amainpackage.a_o2odata_deal.config.config.is_newProduct_path}")
      .dropDuplicates("good_id")
    newProduct_data.createOrReplaceTempView("newProduct_data")

    //处理是否是新品标签
    val mid1 = sqlContext.sql(
      s"""
         |select
         |t1.*,
         |case
         |when t2.good_id is null then false
         |else t2.is_newProduct
         |end as is_newProducts
         |from source_data t1
         |left join
         |newProduct_data t2
         |on t1.good_id=t2.good_id
       """.stripMargin).drop("is_newProduct").withColumnRenamed("is_newProducts","is_newProduct")
    mid1.createOrReplaceTempView("mid1")

    mid1.agg(count("*"),sum("sellCount"),sum("salesAmount")).show(false)

    //取出下架商品
    val xiajia_data = sqlContext.read.json(a_aa_amainpackage.a_o2odata_deal.config.config.source_obs_data_path)
      .where("is_onsell='false'").selectExpr("good_id")
    xiajia_data.createOrReplaceTempView("xiajia_data")

    //取出数据中下架的商品
    sqlContext.sql(
      s"""
         |select
         |t1.good_id
         |from mid1 t1
         |inner join
         |xiajia_data t2
         |on t1.good_id=t2.good_id
       """.stripMargin).createOrReplaceTempView("xj_data")

    //加载近三个月数据
    sqlContext.read.orc(s"s3a://dws-data/g_data/2019/11/tmall/").selectExpr("good_id","salesAmount","sellCount")
      .createOrReplaceTempView("data11")

    val data11 = sqlContext.sql(
      s"""
         |select
         |good_id,
         |cast(salesAmount as double)/cast(sellCount as double) as priceText
         |from data11
       """.stripMargin).selectExpr("good_id","priceText")

    sqlContext.read.orc(s"s3a://dws-data/g_data/2019/10/tmall/").selectExpr("good_id","salesAmount","sellCount")
      .createOrReplaceTempView("data10")

    val data10 = sqlContext.sql(
      s"""
         |select
         |good_id,
         |cast(salesAmount as double)/cast(sellCount as double) as priceText
         |from data10
       """.stripMargin).selectExpr("good_id","priceText")

    sqlContext.read.orc(s"s3a://dws-data/g_data/2019/12/tmall/").selectExpr("good_id","salesAmount","sellCount")
      .createOrReplaceTempView("data9")

    val data9 = sqlContext.sql(
      s"""
         |select
         |good_id,
         |cast(salesAmount as double)/cast(sellCount as double) as priceText
         |from data9
       """.stripMargin).selectExpr("good_id","priceText")

    val th_data = data9.unionAll(data10).unionAll(data11)
    th_data.createOrReplaceTempView("th_data")
    sqlContext.sql(
      s"""
         |select
         |good_id,
         |priceText
         |from
         |(select
         |*,
         |row_number() over(partition by good_id order by cast(priceText as double) asc) as rank
         |from th_data)
         |where rank=1
       """.stripMargin).createOrReplaceTempView("hsitory_data")

    sqlContext.sql(
      s"""
         |select
         |t1.good_id,
         |t2.priceText
         |from xj_data t1
         |inner join
         |hsitory_data t2
         |on t1.good_id=t2.good_id
       """.stripMargin).dropDuplicates("good_id").createOrReplaceTempView("price_data")

    val mid2 = sqlContext.sql(
      s"""
         |select
         |*,
         |cast(price as double)*cast(sellCount as bigint) as sales
         |from
         |(select
         |t1.*,
         |case
         |when t2.good_id is null then t1.priceText
         |else min_price(t1.priceText,t2.priceText)
         |end as price
         |from mid1 t1
         |left join
         |price_data t2
         |on t1.good_id=t2.good_id)
       """.stripMargin).drop("priceText","salesAmount")
      .withColumnRenamed("price","priceText")
      .withColumnRenamed("sales","salesAmount")
    mid2.createOrReplaceTempView("mid2")

    mid2.agg(count("*"),sum("sellCount"),sum("salesAmount")).show(false)

    mid2.drop("priceText").withColumnRenamed("priceText_v1","priceText")
     .repartition(8).write.orc(s"${source_result}_modify_xiajia")

    //加载补采的价格数据
   /* sqlContext.read.json(s"s3a://o2o-sourcedata/obs-source-2019/12/tmall/tmall_mmm_app_1912/").dropDuplicates("good_id")
      .createOrReplaceTempView("price_data1")


    val result = sqlContext.sql(
      s"""
         |select
         |*,
         |cast(price as double)*cast(sellCount as bigint) as sales
         |from
         |(select
         |t1.*,
         |case
         |when t2.good_id is null then t1.priceText
         |else min_price(t1.priceText,t2.zdPrice)
         |end as price
         |from mid2 t1
         |left join
         |price_data1 t2
         |on t1.good_id=t2.good_id)
       """.stripMargin).drop("priceText","salesAmount")
      .withColumnRenamed("price","priceText")
      .withColumnRenamed("sales","salesAmount")

    result.agg(count("*"),sum("sellCount"),sum("salesAmount")).show(false)


    result.drop("priceText").withColumnRenamed("priceText_v1","priceText").repartition(8).write.
      orc(s"s3a://o2o-dataproces-group/han_congcong/2019/12/tmall_brandresult_modify_v5")*/
  }

  def min_price(price1:String,price2:String):String={
    var p = 0.0
    val p1 = price1.toDouble
    val p2 = price2.toDouble
    if (p1<=p2){
      p = p1
    }else{
      p = p2
    }
    p.toString
  }

}
